import pandas as pd
import os
import statsmodels.api as sm
from sklearn.metrics import mean_squared_error
from math import sqrt

df = pd.read_excel(os.getcwd() + "\\data\\1647848272130494.xlsx", header=0)
res_pth = os.getcwd() + "\\data\\predict_task3.xlsx"
if (os.path.exists(res_pth)):
    os.remove(res_pth)
data_df = pd.DataFrame()
c = pd.ExcelWriter(res_pth)
data_df.to_excel(c)
c.save()
i = 1000000001
rmse_s = 500  # 设置初始均方根误差值
while True:
    ClientData = df.loc[df["用户编号"] == i]
    train = ClientData[:]
    test = ClientData[:]
    ts_ARIMA = train['缴费金额（元）'].astype('float64')
    j = 0
    rmse_s = 500
    while True:
        j += 1
        a1 = int(j % 10)
        a2 = int(j % 100 / 10)
        a3 = int(j / 100)
        try:
            fit1 = sm.tsa.ARIMA(ts_ARIMA, order=(a1, a2, a3)).fit()  # a1,a2,a3
            data_len = ts_ARIMA.count() - 1
            y_hat_ARIMA = fit1.predict(start=0, end=data_len, dynamic=False)

            rmse = sqrt(mean_squared_error(test['缴费金额（元）'], y_hat_ARIMA.to_frame()))
            print("##################################################", rmse, a1, a2, a3, str(i))
            if rmse < rmse_s:  # 均方根检验，找出最优模型
                rmse_s = rmse
                y_hat_ARIMA = fit1.predict(start=0, end=11, dynamic=False)
                med = pd.Series(y_hat_ARIMA)
                file_xls = pd.read_excel(res_pth)
                file_xls['客户' + str(i)] = med
                file_xls.to_excel(res_pth, sheet_name='预测未来12个月的缴费金额', index=False, header=True)
                # break  # 提前结束
        except:
            pass
        if j > 111:  # 为777时需运行6个小时
            break
    i += 1
    if i > 1000000100:
        break
